set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

--发票审核 1=财,2=转,3=银
CREATE PROCEDURE [dbo].[sp_jiuqi_voucher_invoice] @kpid int,@isignseq int output,@csign varchar(100) output

AS


declare @iszb int

declare @compid int,@custid int,@type int,@kpje numeric(18,2),@jsje numeric(18,2),@taxje numeric(18,2) ,@bizDate smalldatetime

--公司+供应商+类型(1=进项，2=销项，3=物流)
select @compid=dbo.gf_sys_cwcompid_by_compid(compid),@custid=custid,@type=type,@bizDate=dt from t_cw_kp where kpid=@kpid
select @iszb=isnull(iszb,0) from t_sys_comp where compid=@compid

select @isignseq=dbo.gf_cw_sys_isignseq(@compid,2)
select @csign=dbo.gf_cw_sys_csign(dbo.gf_sys_cwcompid_by_compid(@compid),@isignseq)

--销售费用
if @type=3 and exists(select 1 from t_cw_wlfy where kpid=@kpid and xxtype<>1)
  begin
    exec [sp_u8_fpinput_list_xsfy] @kpid,@isignseq output,@csign output
    return
  end

if @type in (1,2)
  select @kpje=sum(kpje),@jsje=sum(jsje),@taxje=sum(taxje) from t_cw_ykp where kpid=@kpid
else
  select @kpje=sum(isnull(je1,0)-isnull(taxje,0)),@jsje=sum(isnull(je1,0)),@taxje=sum(isnull(taxje,0)) from t_wl_ykp where kpid=@kpid

declare @custname varchar(100)
if @type in (1,3)
  select @custname=dbo.gf_custid_fullname(@custid)
else if @type in(2)
  select @custname=dbo.gf_custid_fullname(@custid)



--摘要
declare @note varchar(255)
select @note=(case when @type=1 then '应付'+@custname+'料款'
              when @type=2 then '应收'+@custname+'料款'
              when @type=3 then '应付'+@custname+'运输费' end)

declare @ccode1 varchar(100),@ccname1 varchar(100),@je1 numeric(18,2),@isadd1 int,@isdept1 int
declare @ccode2 varchar(100),@ccname2 varchar(100),@je2 numeric(18,2),@isadd2 int,@isdept2 int
declare @ccode3 varchar(100),@ccname3 varchar(100),@je3 numeric(18,2),@isadd3 int,@isdept3 int
declare @ccode4 varchar(100),@ccname4 varchar(100),@je4 numeric(18,2)

declare @csup_id1 varchar(100),@csup_id2 varchar(100),@csup_id3 varchar(100)
declare @ccus_id1 varchar(100),@ccus_id2 varchar(100),@ccus_id3 varchar(100)
declare @ccus_id4 varchar(100) --代垫运费

declare @dsdfje numeric(18,2),@xscustid int

select @isdept1=0,@isdept2=0,@isdept3=0

--进项
if @type=1
  begin
--代垫运费
    select @dsdfje=dsdfje from t_cw_kp where kpid=@kpid
    select top 1 @xscustid=c.custid from t_cg_buy2 a,t_cg_buy1 b,t_xs_sale1 c where a.kpid=@kpid and a.otherje>0 and a.buyid=b.buyid and b.saleid=c.saleid

--库存商品
    select @ccode1=dbo.gf_cw_sys_kpgoods_ccode(@kpid),
      @ccname1=dbo.gf_cw_sys_code_name(@compid,dbo.gf_cw_sys_kpgoods_ccode(@kpid)),
      @je1=@kpje

--进项税-17
    select @ccode2=dbo.gf_cw_sys_kprate_ccode(@kpid),
      @ccname2=dbo.gf_cw_sys_code_name(@compid,dbo.gf_cw_sys_kprate_ccode(@kpid)),
      @je2=@taxje

--应付账款/商品款
    select @ccode3=(select code from t_jiuqi_common_subject_code where company_id=@compid and code_id=300),--select u8code from t_u8_code where compid=@compid and codeid=300),
      @ccname3=(select subject_name from t_jiuqi_common_subject where code_id=300),--select name from v_u8_code where codeid=300),
      @je3=isnull(@kpje,0)+isnull(@taxje,0)+isnull(@dsdfje,0)

--select @isdept3=(case when @iszb=1 then 1 else @isdept3 end)

--代垫运费
    select @ccode4=(select code from t_jiuqi_common_subject_code where company_id=@compid and code_id=1400),--select u8code from t_u8_code where compid=@compid and codeid=1400),
      @ccname4=(select subject_name from t_jiuqi_common_subject where code_id=1400),--select name from v_u8_code where codeid=1400),
      @je4=isnull(@dsdfje,0),
      @ccus_id4=(select u8code from t_u8_customer where compid=@compid and custid=@xscustid)


    select @isadd1=0,@isadd2=0,@isadd3=1
    select @csup_id1=null,@csup_id2=null,@csup_id3=(select code from t_jiuqi_supplier_mapping where company_id=@compid and supplier_id=@custid)--select u8code from t_u8_vendor where compid=@compid and custid=@custid)
    select @ccus_id1=null,@ccus_id2=null,@ccus_id3=null

--内部经营(挂其他应付款)
    if exists(select 1 from t_sys_comp where compid=@custid)
       and exists(select 1 from t_sys_comp where compid in (@compid,@custid))
      begin
        select @ccode3=(select code from t_jiuqi_company_mapping where company1_id=dbo.gf_sys_cwcompid_by_compid(@compid) and company2_id=dbo.gf_sys_cwcompid_by_compid(@custid)),--select u8code from t_u8_compcode where compid1=dbo.gf_sys_cwcompid_by_compid(@compid) and compid2=dbo.gf_sys_cwcompid_by_compid(@custid)),
          @ccname3=(select dbo.gf_cw_sys_code_name(company1_id,code) from t_jiuqi_company_mapping where company1_id=dbo.gf_sys_cwcompid_by_compid(@compid) and company2_id=dbo.gf_sys_cwcompid_by_compid(@custid)),--select dbo.gf_cw_sys_code_name(compid1,u8code) from t_u8_compcode where compid1=dbo.gf_sys_cwcompid_by_compid(@compid) and compid2=dbo.gf_sys_cwcompid_by_compid(@custid)),
          @note='应付'+dbo.gf_compid_fullname(@custid)+'内部经营款'
        select @isdept3=0
      end

--select @isdept3=0
  end

--销项
if @type=2
  begin
    select @xscustid=custid,@dsdfje=dsdfje from t_cw_kp where kpid=@kpid

--应收账款
    select @ccode1=(select code from t_jiuqi_common_subject_code where company_id=@compid and code_id=100 ),--(select u8code from t_u8_code where compid=@compid and codeid=100),
      @ccname1=(select subject_name from t_jiuqi_common_subject where code_id=100), --(select name from v_u8_code where codeid=100),
      @je1=@jsje+isnull(@dsdfje,0)

    select @isdept1=0--(case when @iszb=1 then 1 else @isdept1 end)

	--销项税
    select @ccode2=dbo.gf_cw_sys_kprate_ccode(@kpid),
      @ccname2=dbo.gf_cw_sys_code_name(@compid,dbo.gf_cw_sys_kprate_ccode(@kpid)),
      @je2=@taxje

--主营业务收入
    select @ccode3= (select code from t_jiuqi_common_subject_code where company_id=@compid and code_id=1100),--(select u8code from t_u8_code where compid=@compid and codeid=1100),
      @ccname3=(select subject_name from t_jiuqi_common_subject where code_id=1100) , --(select name from v_u8_code where codeid=1100),
      @je3=@kpje

    select @ccname1=@ccname1+'/'+dbo.gf_custid_fullname(@custid)

--代垫运费
    select @ccode4=(select code from t_jiuqi_common_subject_code where company_id=@compid and code_id=1400),--(select u8code from t_u8_code where compid=@compid and codeid=1400),
      @ccname4=(select subject_name from t_jiuqi_common_subject where code_id=1400),--(select name from v_u8_code where codeid=1400),
      @je4=isnull(@dsdfje,0),
      @ccus_id4=(select code from t_jiuqi_customer_mapping where company_id=@compid and customer_id=@xscustid)--select u8code from t_u8_customer where compid=@compid and custid=@xscustid)


    select @isadd1=1,@isadd2=0,@isadd3=0
    select @csup_id1=null,@csup_id2=null,@csup_id3=null
    select @ccus_id1=(select code from t_jiuqi_customer_mapping where company_id=@compid and customer_id=@xscustid),@ccus_id2=null,@ccus_id3=null--select u8code from t_u8_customer where compid=@compid and custid=@custid),@ccus_id2=null,@ccus_id3=null

	--内部经营(挂其他应付款)
    if exists(select 1 from t_sys_comp where compid=@custid)
       and exists(select 1 from t_sys_comp where compid in (@compid,@custid) and isnull(iszb,0)=1)
      begin
        select @ccode1=(select code from t_jiuqi_company_mapping where company1_id=dbo.gf_sys_cwcompid_by_compid(@compid) and company2_id=dbo.gf_sys_cwcompid_by_compid(@custid)),--select u8code from t_u8_compcode where compid1=dbo.gf_sys_cwcompid_by_compid(@compid) and compid2=dbo.gf_sys_cwcompid_by_compid(@custid)),
          @ccname1=(select dbo.gf_cw_sys_code_name(company1_id,code) from t_jiuqi_company_mapping where company1_id=dbo.gf_sys_cwcompid_by_compid(@compid) and company2_id=dbo.gf_sys_cwcompid_by_compid(@custid)),--select dbo.gf_cw_sys_code_name(compid1,u8code) from t_u8_compcode where compid1=dbo.gf_sys_cwcompid_by_compid(@compid) and compid2=dbo.gf_sys_cwcompid_by_compid(@custid)),
          @note='应收'+dbo.gf_compid_fullname(@custid)+'内部经营款'
        select @isdept1=0
      end

    select @isdept3=1
  end

--物流
if @type=3
  begin
    declare @buyid int,@cgfycodeid int
    select top 1 @buyid=xxid from t_cw_wlfy where kpid=@kpid
    select top 1 @cgfycodeid=codeid from t_cw_wlfy where kpid=@kpid

--库存商品
    select @ccode1=(select code from t_jiuqi_common_subject_code where company_id=@compid and code_id=@cgfycodeid),--select u8code from t_u8_code where compid=@compid and codeid=@cgfycodeid),
      @ccname1=(select subject_name from t_jiuqi_common_subject where code_id=@cgfycodeid),--select name from v_u8_code where codeid=@cgfycodeid),
      @je1=@kpje

--进项税科目
    declare @u8code varchar(100)
    select top 1 @u8code=b.feeu8code from t_cw_wlfy a,t_cw_feetype b where a.kpid=@kpid and a.ftid1=b.ftid and a.taxje>0

    select @ccode2=@u8code,
      @ccname2=(select ccode_name from t_cw_sys_code where compid=dbo.gf_sys_cwcompid_by_compid(@compid) and ccode=@u8code),
      @je2=@taxje



--禁止存在两个税科目
    if (select count(distinct b.ftid) from t_cw_wlfy a,t_cw_feetype b where a.kpid=@kpid and a.ftid1=b.ftid and a.taxje>0)>1
      select @ccname2='',@ccode2=''


    declare @issjf int
    select @issjf=0
    if exists(select 1 from t_cw_wlfy a inner join t_cw_feetype b on a.ftid1=b.ftid and a.kpid=@kpid and isnull(b.issjf,0)=1)
      select @issjf=1

--应付账款/运费
    select @ccode3=(select code from t_jiuqi_common_subject_code where company_id=@compid and code_id=(case when @issjf=0 then 400 else 410 end)),--select u8code from t_u8_code where compid=@compid and codeid=(case when @issjf=0 then 400 else 410 end)),
      @ccname3=(select subject_name from t_jiuqi_common_subject where code_id=(case when @issjf=0 then 400 else 410 end)),--select name from v_u8_code where codeid=(case when @issjf=0 then 400 else 410 end)),
      @je3=@jsje

    select @isadd1=0,@isadd2=0,@isadd3=1
    select @csup_id1=null,@csup_id2=null,@csup_id3=(select code from t_jiuqi_supplier_mapping where company_id=@compid and supplier_id=@custid)--select u8code from t_u8_vendor where compid=@compid and custid=@custid)
    select @ccus_id1=null,@ccus_id2=null,@ccus_id3=null

--select @isdept3=(case when @iszb=1 then 1 else @isdept3 end)
  end


select
  note=@note,
  ccode=@ccode1,
  ccname=@ccname1,
  md=@je1,
  mc=0.00,
  ismd=1,
  ismc=0,
  ccus_id=@ccus_id1,
  csup_id=@csup_id1,
  isadd=@isadd1,
  isdept=@isdept1,
  isproc=0,
  bizDate=@bizDate
where @je1<>0

union all

select
  note=@note,
  ccode=@ccode2,
  ccname=@ccname2,
  md=(case when @type in (1,3) then @je2 else 0.00 end),
  mc=(case when @type in (1,3) then 0.00 else @je2 end),
  ismd=(case when @type in (1,3) then 1 else 0 end),
  ismc=(case when @type in (1,3) then 0 else 1 end),
  ccus_id=@ccus_id2,
  csup_id=@csup_id2,
  isadd=@isadd2,
  isdept=@isdept2,
  isproc=0 ,
  bizDate=@bizDate
where @je2<>0

union all

select
  note=@note,
  ccode=@ccode4,
  ccname=@ccname4,
  md=(case when @type=1 then @je4 else 0.00 end),
  mc=(case when @type=1 then 0.00 else @je4 end),
  ismd=(case when @type=1 then 1 else 0 end),
  ismc=(case when @type=1 then 0 else 1 end),
  ccus_id=@ccus_id4,
  csup_id=null,
  isadd=0,
  isdept=0,
  isproc=0,
  bizDate=@bizDate
where @type in (1,2) and @je4<>0

union all

select
  note=@note,
  ccode=@ccode3,
  ccname=@ccname3,
  md=0.00,
  mc=@je3,
  ismd=0,
  ismc=1,
  ccus_id=@ccus_id3,
  csup_id=@csup_id3,
  isadd=@isadd3,
  isdept=@isdept3,
  isproc=0,
  bizDate=@bizDate
where @je3<>0